import csv
import datetime
from io import StringIO

from flask import Response
from flask import jsonify, request

from tzq.database_compare_system import dcs_bp
from tzq.database_compare_system.models.dcs_common_models import TableInfo
from tzq.extensions import db


# 数据接口 - 支持分页和导出
# 获取数据接口 (Layui表格格式)
@dcs_bp.route('/api/tableInfos', methods=['GET', 'POST'])
def get_table_infos():
    # 检查是否是导出请求
    export = request.args.get('export')
    # 导出请求
    if export == '1':
        source = request.values.get("source")
        schema = request.values.get("schema")
        table_name = request.values.get("table_name")
        column_name = request.values.get("column_name")
        # 获取所有数据
        data = TableInfo.query

        # 应用搜索条件
        if source:
            data = data.filter(TableInfo.source.ilike(f'%{source}%'))
        if schema:
            data = data.filter(TableInfo.schema.ilike(f'%{schema}%'))
        if table_name:
            data = data.filter(TableInfo.table_name.ilike(f'%{table_name}%'))
        if column_name:
            data = data.filter(TableInfo.column_name.ilike(f'%{column_name}%'))

        # 获取排序参数
        sort_field = request.args.get('field', 'table_info_id')
        sort_order = request.args.get('order', 'asc')

        # 构建排序
        if hasattr(TableInfo, sort_field):
            if sort_order == 'asc':
                order_by = getattr(TableInfo, sort_field).asc()
            else:
                order_by = getattr(TableInfo, sort_field).desc()
            data = data.order_by(order_by)
        else:
            data = data.order_by(TableInfo.table_info_id.asc())

        # 分页查询
        data = data.paginate(page=1, per_page=1000000, error_out=False)

        # 创建内存文件对象
        output = StringIO()
        output.write('\ufeff')  # 添加UTF-8 BOM头

        # 创建CSV写入器
        writer = csv.writer(output, quoting=csv.QUOTE_ALL)

        # 写入表头
        headers = [
            'table_info_id', 'source', 'database_type', 'schema', 'owner', 'table_name', 'table_comment', 'column_num',
            'column_name', 'data_type', 'data_length', 'data_default', 'nullable', 'pkey', 'column_comment',
            'creation_date'
        ]  # 使用中文表头
        writer.writerow(headers)

        # 写入数据行
        for item in data:
            row = [
                item.table_info_id,
                item.source,
                item.database_type,
                item.schema,
                item.owner,
                item.table_name,
                item.table_comment,
                item.column_num,
                item.column_name,
                item.data_type,
                item.data_length,
                item.data_default,
                item.nullable,
                item.pkey,
                item.column_comment,
                item.creation_date
                # 添加其他字段...
            ]
            writer.writerow(row)

        date_string = datetime.datetime.now().strftime("%Y%m%d-%H%M%S")

        # 创建响应对象
        response = Response(
            output.getvalue(),
            mimetype="text/csv; charset=utf-8",
            headers={
                "Content-disposition": "attachment; filename=" + date_string + "-table_info_export.csv",
                "Content-type": "text/csv; charset=utf-8"
            }
        )
        return response

    # 分页请求
    else:
        page = int(request.args.get('page', 1))
        limit = int(request.args.get('limit', 10))

        # 获取搜索参数
        # source = request.args.get("source")
        source = request.values.get("source")
        schema = request.values.get("schema")
        table_name = request.values.get("table_name")
        column_name = request.values.get("column_name")

        # 基础查询
        query = TableInfo.query

        # 应用搜索条件
        if source:
            query = query.filter(TableInfo.source.ilike(f'%{source}%'))
        if schema:
            query = query.filter(TableInfo.schema.ilike(f'%{schema}%'))
        if table_name:
            query = query.filter(TableInfo.table_name.ilike(f'%{table_name}%'))
        if column_name:
            query = query.filter(TableInfo.column_name.ilike(f'%{column_name}%'))

        # 获取排序参数
        sort_field = request.args.get('field', 'table_info_id')
        sort_order = request.args.get('order', 'asc')

        # 构建排序
        if hasattr(TableInfo, sort_field):
            if sort_order == 'asc':
                order_by = getattr(TableInfo, sort_field).asc()
            else:
                order_by = getattr(TableInfo, sort_field).desc()
            query = query.order_by(order_by)
        else:
            query = query.order_by(TableInfo.table_info_id.asc())

        # 分页查询
        pagination = query.paginate(page=page, per_page=limit, error_out=False)

        # 构建Layui要求的JSON格式
        return jsonify({
            'code': 0,
            'msg': '',
            'count': pagination.total,
            'data': [tableInfo.to_dict() for tableInfo in pagination.items]
        })


@dcs_bp.route('/api/getSources', methods=['GET'])
def get_source_data():
    """
    从PostgreSQL获取 source 下拉框的数据，提供json数据的api接口
    “表字段信息”查询页面 的 “数据源”下拉框 展示
    """
    try:
        # 查询去重后的 source 字段值
        distinct_sources = db.session.query(TableInfo.source).distinct().all()
        # 提取结果（因为返回的是元组列表）
        sources_data = [{"source": item[0]} for item in distinct_sources]  # 提取元组中的第一个元素
        return jsonify({"code": 0, "msg": "", "count": len(sources_data), "data": sources_data})
    except Exception as e:
        print("Database error:", e)
        return jsonify({"code": -1, "msg": str(e), "count": 0, "data": None})
